package com.apes.scm.masterdata.finance.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

@Component
public class BankAccountDao extends ApesDao {

    /**
     * 分页查询
     *
     * @param param
     * @return
     */
    public JSONArray findBankAccount(JSONObject param, String company,int page, int size) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( " +
                "SELECT a.id, a.name,(SELECT b.name FROM mas_bank b where a.bank_id = b.id) bank,\n" +
                "   a.bank_branch, a.account_id, a.account_name, a.receipt_sign,a.pay_sign,\n" +
                "   (SELECT P.NAME FROM MAS_PARTY_ROLE T LEFT JOIN MAS_PARTY P ON T.PARTY_ID = P.ID WHERE T.ID = A.CREATOR_ID) CREATOR_ID,\n" +
                "   to_char(a.create_date, 'yyyy-mm-dd') create_date, \n" +
                "   (SELECT P.NAME  FROM MAS_PARTY_ROLE T LEFT JOIN MAS_PARTY P ON T.PARTY_ID = P.ID\n" +
                "   WHERE T.ID = A.updater_id) updater_id, to_char(a.update_date, 'yyyy-mm-dd') update_date, a.company_id, a.valid\n" +
                "  FROM mas_bank_account a ,mas_bank b where a.bank_id=b.id ");
        if (param.get("ids") != null) {
            sb.append(" AND (a.account_name like '%"+param.get("ids")+"%' or b.name like '%"+param.get("ids")+"%' or a.account_id like '%"+param.get("ids")+"%')");
        }
        if (param.get("company_id") != null) {
            sb.append(" AND a.company_id =:company_id");
        }
        if (param.get("valid") != null) {
            sb.append(" AND a.valid =:valid");
        }
        if (param.get("role_id") != null) {
            sb.append(" AND (SELECT P.ROLE_ID FROM MAS_PARTY_ROLE P WHERE P.ID = a.company_id) = :role_id");
        }
        if (!"A0000".equals(company)) {
            sb.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.company_id = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        sb.append(" order by a.create_date desc  ) CB WHERE ROWNUM <="+ (page == 0? size : (page +1)* size)+" )T_CB WHERE T_CB.rowno >"+((page+1)-1) * size);
        return toJSONArray(find(sb.toString(), param));

    }


    /**
     * 分页查询
     *
     * @param param
     * @return
     */
    public Object findBankAccountCount(JSONObject param, String company) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT count(1) count FROM mas_bank_account a where 1=1 \n");
        if (param.get("ids") != null) {
            sb.append(" AND a.account_name =:ids or a.bank_id = :ids or a.account_id = :ids");
        }
        if (param.get("company_id") != null) {
            sb.append(" AND a.company_id =:company_id");
        }
        if (param.get("valid") != null) {
            sb.append(" AND a.valid =:valid");
        }
        if (param.get("role_id") != null) {
            sb.append(" AND (SELECT P.ROLE_ID FROM MAS_PARTY_ROLE P WHERE P.ID = a.company_id) = :role_id");
        }
        if (!"A0000".equals(company)) {
            sb.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.company_id = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        sb.append(" order by a.create_date desc ");
        return toJSONArray(find(sb.toString(), param));
    }

}
